﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using Longbow.Caching;

namespace HuaCheng.DataAccess
{
    /// <summary>
    /// 用户信息的数据库操作
    /// </summary>
    public class UserInfoHelper
    {
        internal const string RetrieveUserinfoDataKey = "UserInfoHelper-RetrieveUserinfo";
        internal const string RetrieveUsernameDataKey = "UserInfoHelper-RetrieveUsername";
        internal const string RetrieveUsernameBygroupDataKey = "UserInfoHelper-RetrieveUsernameBygroupname";
        //验证用户名是否存在
        public static bool IsExist(string username)
        {
            bool UserFlag = false;
            List<String> nameSet;
            nameSet = UserInfoHelper.RetrieveUsername();
            if (nameSet.Where(o => o == username).ToList().Count > 0)
            {
                UserFlag = true;
            }
            return UserFlag;
        }
        /// <summary>
        /// 检查用户组下是否存在用户
        /// </summary>
        /// <param name="groupname"></param>
        /// <returns></returns>
        public static bool HasUser(string groupname)
        {
            bool HasFlag=false;
            List<String> nameSet;
            nameSet=UserInfoHelper.RetrieveUsernameBygroupname(groupname);
            if (nameSet.Count > 0)
            {
                HasFlag = true;
            }
            return HasFlag;
        }
        /// <summary>
        /// 新增用户信息
        /// </summary>
        /// <param name="userinfo"></param>
        public static void InsertUser(UserInfo userinfo)
        {
            string key = string.Format("{0}-{1}", RetrieveUsernameBygroupDataKey, userinfo.Groupname);
            if (userinfo == null) throw new ArgumentNullException("userinfo");
            string sql = "insert into userinfo(user_name,pass_word,telephone,email,groupname,remark) values (@user_name,@pass_word,@telephone,@email,@groupname,@remark)";
            try 
            {
                using (DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql))
                {
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@user_name", userinfo.UserName));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@pass_word", userinfo.PassWord));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@telephone", userinfo.Telephone));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@email", userinfo.Email));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@groupname", userinfo.Groupname));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@remark", userinfo.Remark));
                    DBAccessManager.SqlDBAccess.ExecuteNonQuery(cmd);
                }
                    CacheManager.Clear(K => K==RetrieveUsernameDataKey);
                    CacheManager.Clear(K => K==key);
            }
            catch (Exception ex) {  Console.WriteLine(ex.Message + ex.StackTrace); }
        }
        /// <summary>
        /// 更新用户信息
        /// </summary>
        /// <param name="userinfo"></param>
        public static void UpdateUser(UserInfo userinfo)
        {
            string key = string.Format("{0}-{1}", RetrieveUserinfoDataKey, userinfo.UserName);
            if (userinfo == null) throw new ArgumentNullException("userInfo");
            string sql =  "update userinfo set telephone=@telephone,email=@email,remark=@remark where user_name=@user_name";
            try
            {
                using (DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql))
                {
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@user_name", userinfo.UserName));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@telephone", userinfo.Telephone));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@email", userinfo.Email));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@remark", userinfo.Remark));
                    DBAccessManager.SqlDBAccess.ExecuteNonQuery(cmd);
                }
                CacheManager.Clear(K => K==key);
            }
            catch (Exception ex) {  Console.WriteLine(ex.Message + ex.StackTrace); }
        }
        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="userinfo"></param>
        public static void UpdatePwd(UserInfo userinfo)
        {
            string key = string.Format("{0}-{1}", RetrieveUserinfoDataKey, userinfo.UserName);
            if (userinfo == null) throw new ArgumentNullException("userinfo");
            string sql = "update userinfo set pass_word=@pass_word where user_name=@user_name";
            try 
            {
                using (DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql))
                {
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@pass_word", userinfo.PassWord));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@user_name", userinfo.UserName));
                    DBAccessManager.SqlDBAccess.ExecuteNonQuery(cmd);
                }
                CacheManager.Clear(K => K == key);
            }
            catch (Exception ex) {  Console.WriteLine(ex.Message + ex.StackTrace); }
        }
        /// <summary>
        /// 按用户名组名查询用户名
        /// </summary>
        /// <param name="groupname"></param>
        /// <returns></returns>
        public static List<string> RetrieveUsernameBygroupname(string groupname)
        {
            string key = string.Format("{0}-{1}", RetrieveUsernameBygroupDataKey, groupname);
            return CacheManager.GetOrAdd(key, k =>
            {
                List<string> username=new List<string>();
                string sql = "select user_name from userinfo where groupname=@groupname";
                DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql);
                try
                {
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@groupname", groupname));
                    using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            username.Add(reader.IsDBNull(0) ? string.Empty : (string)reader["user_name"]) ;
                        }
                    }
                }
                catch (Exception ex) {  Console.WriteLine(ex.Message + ex.StackTrace); }
                return username;
            }, RetrieveUsernameBygroupDataKey);
        }
        /// <summary>
        /// 按用户名查询用户信息
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public static UserInfo RetrieveUserinfo(string username)
        {
            string key = string.Format("{0}-{1}", RetrieveUserinfoDataKey, username);
            UserInfo ret = null;
            ret = CacheManager.GetOrAdd(key, k =>
                {
                    UserInfo userinfo = null;
                    string sql = "select pass_word,telephone,email,groupname,remark from userinfo where user_name=@username";
                    DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql);
                    try
                    {
                        cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@userName", username));
                        using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(cmd))
                        {
                            if (reader.Read())
                            {
                                userinfo = new UserInfo()
                                {
                                    UserName = username,
                                    PassWord = reader.IsDBNull(0) ? string.Empty : (string)reader["pass_word"],
                                    Telephone=reader.IsDBNull(1)?string.Empty:(string)reader["telephone"],
                                    Email = reader.IsDBNull(2) ? string.Empty : (string)reader["email"],
                                    Groupname = reader.IsDBNull(3) ? string.Empty : (string)reader["groupname"],
                                    Remark = reader.IsDBNull(4) ? string.Empty : (string)reader["remark"]
                                };
                            }
                        }
                    }
                    catch (Exception ex) {  Console.WriteLine(ex.Message + ex.StackTrace); }
                    return userinfo;
                }, RetrieveUserinfoDataKey);
            return ret;
        }
        /// <summary>
        /// 查询所有用户名
        /// </summary>
        /// <returns></returns>
        public static List<string> RetrieveUsername()
        {
            string key = RetrieveUsernameDataKey;
            return CacheManager.GetOrAdd(key, k =>
            {
                List<String> ret = new List<string>();
                string sql = "select user_name from userinfo";
                DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql);
                try
                {
                    using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(cmd))
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                ret.Add(Convert.ToString(reader["user_name"]));
                            }
                        }
                    }
                }
                catch (Exception ex) {  Console.WriteLine(ex.Message + ex.StackTrace); }
                return ret;
            }, RetrieveUsernameDataKey);
        }
        /// <summary>
        /// 按用户名删除用户信息
        /// </summary>
        /// <param name="username"></param>
        public static void DeleteuerByname(string username)
        {
            string key = string.Format("{0}-{1}", RetrieveUserinfoDataKey, username);
            try
            {
                string sql = "delete from userinfo where user_name=@user_name";
                using (DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql))
                {
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@user_name", username));
                    DBAccessManager.SqlDBAccess.ExecuteNonQuery(cmd);
                    //清除查询所有用户名的缓存
                    CacheManager.Clear(k => k == RetrieveUsernameDataKey);
                    //清除按用户名查询用户信息的缓存
                    CacheManager.Clear(k=> k==key);
                    //清除按用户组名查询用户名的缓存
                    CacheManager.Clear(k => k .Contains(RetrieveUsernameBygroupDataKey));
                }
            }
            catch (Exception ex)
            {
                 Console.WriteLine(ex.Message + ex.StackTrace);
            }                                                                                               
        }
    }
}
